<head>
    <title>System Options</title>
    <link href="style.css" rel="stylesheet" type="text/css" media="screen" />
</head>
<body>
    <div id="page">
        <div id="logo">
            <h1><a href="#">System Options</a></h1>
        </div>
        <form onsubmit="handleSubmit(); return false;">
            <div id='infotab' name='infotab' style='display:none;'>
                <table>
                    <tr>
                        <td>
                            <input type='button' class="buttons"  id='exitbutton' onclick='location.replace("index.html");' value='Exit' />
                        </td>
                    </tr>
                </table>
                <table class='field'>
                    <tr>
                        <td class='field'>
                            Fix Customer File
                        </td>
                        <td class='field2'>
                            <input type='button' class="buttons"  id='producttypeid' name='producttypeid' onclick='FixCustomers()' value='Go'>
                        </td>
                    </tr>
                    <tr>
                        <td class='field'>
                            Fix Invoices
                        </td>
                        <td class='field2'>
                            <input type='button' class="buttons"  id='producttypeid' name='producttypeid' onclick='FixInvoices()' value='Go'>
                        </td>
                    </tr>
                </table>
            </div>
            <div id='searchtab' style='display:none;'>
                <table class='field'>
                    <tr>
                        <td class='field'>
                            Search:
                        </td>
                        <td class='field2'>
                            <input type='text' id='searcht' name='searcht'>
                        </td>
                    </tr>
                </table>
                <div class='field'>
                    <input type='button' class='buttons' value='Search' onclick='Search(document.all.searcht.value,0);'/><input type='button' value='New' onclick='Load(0);'/><input type='button' value='Exit' onclick='location.replace("index.html");'/>
                </div><span id="status">&nbsp;</span>
            </div>
        </form>
        <!-- ====================================== -->
        <!-- End HTML code.  Begin JavaScript code. -->
        <script type="text/javascript" src="gears_init.js">
        </script>
        <script type="text/javascript" src="scripts.js">
        </script>
        <script>
            
            var db;
            
            init();
            var query = location.search;
            var itemid = query.substring(query.indexOf("id=") + 3, query.length);
            ChangeTab(0);
            //alert(itemid);
            if (itemid != '') {
                // var loadid = new Number(itemid); //converts to number to prevent injection
                // Load(loadid);
            
            }
            else {
                //document.all.producttypeid.value = '';
                //Search('', 0);
            }
            
            
            function ChangeTab(item){
            
            
                if (item == 0) 
                    document.all.infotab.style.display = 'inline';
                else 
                    document.all.infotab.style.display = 'none';
                if (item == 1) //search
                    document.all.searchtab.style.display = 'inline';
                else 
                    document.all.searchtab.style.display = 'none';
                
            }
            
            // Open this page's local database.
            function init(option){
                var success = false;
                
                if (window.google && google.gears) {
                    try {
                        db = google.gears.factory.create('beta.database');
                        
                        if (db) {
                            db.open('psdb');
                            if (option) //reset db
                                db.execute('drop table ProductType');
                            // db.execute('');
                            
                            success = true;
                            // Initialize the UI at startup.
                        
                        }
                        
                    } 
                    catch (ex) {
                        setError('Could not create database: ' + ex.message);
                        alert('Could not create database: ' + ex.message);
                    }
                }
                
                // Enable or disable UI elements
                
                var inputs = document.forms[0].elements;
                for (var i = 0, el; el = inputs[i]; i++) {
                    el.disabled = !success;
                }
                
            }
            
            
            function Delete(id){
                var rs = db.execute("select typename from ProductType where id='" + id + "'");
                
                if (confirm('Delete type: ' + rs.field(0) + '?') == true) {
                    db.execute("delete from ProductType where id='" + id + "'");
                    alert('Product Type deleted.');
                }
            }
            
            function FixCustomers(){
          
		  		
                var query = "select id from (select distinct count(id) as dups, id from customer group by id) as a where dups >1"
                
				var rs = db.execute(query);
				
                
                while (rs.isValidRow()) {
                    var id = rs.field(0);
				
					try {
						var rs2 = db.execute('select * from Customer where id=' + id);
						
						var firstname = rs2.field(1);
						var lastname = rs2.field(2);
						var middlename = rs2.field(3);
						var phone = rs2.field(4);
						var phone2 = rs2.field(5);
						var email = rs2.field(6);
						var address1 = rs2.field(7);
						var address2 = rs2.field(8);
						var city = rs2.field(10);
						var zipcode = rs2.field(9);
						var birthdate = rs2.field(11);
						var balance = rs2.field(12);
						var creditlimit = rs2.field(13);
						db.execute('delete from customer where id='+ id);
						db.execute('insert into Customer (id, firstname, middlename, lastname, phone, phone2, email, address1, address2, city, zipcode, birthdate, balance, creditlimit) values (? ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [id, firstname, middlename, lastname, phone, phone2, email, address1, address2, city, zipcode, birthdate, balance, creditlimit]);
						rs2.close;
					}
					catch(ex)
					{ alert(ex);}
                    rs.next();
                    
                }
                rs.close();
                alert('Done!')
                // query = "update customer set phone='' where phone is null;";
                //db.execute(query);
                
                return;
                var query = "delete from customer where firstname='undefined'"
                db.execute(query);
                var query = "select * from customer";
                var rs = db.execute(query);
                var id = new Number();
                id = 1;
                
                while (rs.isValidRow()) {
                
                
                    db.execute("update customer set id=? where firstname=? and lastname=?", [id, rs.field(1), rs.field(2)]);
                    
                    rs.next();
                    id++;
                }
                
                rs.close();
                
                query = "update customer set phone='' where phone is null;";
                db.execute(query);
                query = "update customer set phone2=''  where phone2 is null;";
                db.execute(query);
                query = "update customer set address1='' where address1 is null;";
                db.execute(query);
                query = "update customer set address2='' where address2 is null;";
                db.execute(query);
                query = "update customer set city='' where city is null;";
                db.execute(query);
                query = "update customer set zipcode='' where zipcode is null;";
                db.execute(query);
                query = "update customer set birthdate='' where birthdate is null;";
                db.execute(query);
                query = "update customer set email='' where email is null;";
                db.execute(query);
                
                
                
                alert('Finished');
                
            }
            
            function FixInvoices(){
            
                db.execute("delete from sale_2009 where saldate='undefined';");
                var query = "select id,saledate,customerid from sale_2009 where id = 1;";
                var rs = db.execute(query);
                var id = new Number();
                id = 37869;
                
                while (rs.isValidRow()) {
                
                
                    db.execute("update sale_2009 set id=? where saledate=? and customerid=?", [id, rs.field(1), rs.field(2)]);
                    
                    rs.next();
                    id++;
                }
                
                rs.close();
                
                alert('Finished');
                
            }
            
            function Save(){
                if (!google.gears.factory || !db) {
                    return;
                }
                var producttypeidT = document.all.producttypeid;
                var typenameT = document.all.typename;
                var taxrate1T = document.all.taxrate1;
                var taxrate2T = document.all.taxrate2;
                
                var producttypeid = producttypeidT.value;
                if (producttypeid == '') {
                    alert('TypeId is required');
                    return;
                }
                var typename = typenameT.value;
                if (typename == '') {
                    alert('TypeName is required');
                    return;
                }
                var taxrate1 = taxrate1T.value;
                var taxrate2 = taxrate2T.value;
                
                if (isNew == true) {
                    // Insert the new item.
                    //get new id
                    
                    // The Gears database automatically escapes/unescapes inserted values.
                    db.execute('insert into ProductType (id, typename, taxrate1, taxrate2) values (?,?,?,?)', [producttypeidT, typename, taxrate1, taxrate2]);
                    alert('Product Type added.');
                    
                }
                else {
                    db.execute('update ProductType Set typename=?, taxrate1=?, taxrate2=? Where id=?', [typename, taxrate1, taxrate2, producttypeid]);
                    alert('Product Type updated.');
                }
                isNew = false;
                
            }
            
            function Search(search, option){
                //alert('In search!');
                searchBookmark = searchBookmark + option;
                if (searchBookmark < 0) 
                    searchBookmark = 0;
                ChangeTab(1);
                var query = 'select * from ProductType where typename like "' + search + '%"';
                query += ' limit 10 offset ' + searchBookmark;
                
                var status = document.all.status;
                
                var html = '<div class="field" >Search Results:</div>';//'Searching...' ;
                var rs = db.execute(query);
                var count = 0;
                while (rs.isValidRow()) {
                
                
                    html += SearchResult4("'" + rs.field(0) + "'", rs.field(1), 'TaxA: ' + formatNumber(rs.field(2), false), 'TaxB: ' + formatNumber(rs.field(3), false));
                    count++;
                    rs.next();
                }
                if (searchBookmark >= 10) 
                    html += "<a href=# onclick='Search(\"" + search + "\",-10);'>[Back]</a>";
                if (count >= 10) 
                    html += "<a href=# onclick='Search(\"" + search + "\",10);'>[Next]</a>";
                
                status.innerHTML = html;
                
                rs.close();
            }
        </script>
        </body>
    </html>
